
Use NEXUS_DB

GO
--------------------------------------Insert---------------
CREATE PROC insertPlan
	@planName nvarchar(255) ,
	@planDescription nvarchar(500),
	@productID int 
AS
INSERT INTO  [Plan](planName,planDescription,productID) VALUES(@planName,@planDescription,@productID)

GO
---------------------------------------------------update-------------------
CREATE PROC updatePlan
	@planID int ,
	@planName nvarchar(255) ,
	@planDescription nvarchar(500),
	@productID int 
AS
UPDATE [Plan] SET	
	planName =@planName,
	planDescription =@planDescription,
	productID=@productID
WHERE planID=@planID
GO
---------------------------------------Delete--------------------

CREATE PROC deletePlan
	@planID int
AS
	DELETE FROM [Plan] WHERE  planID=@planID
GO

----------------------------------------------getAll
CREATE PROC getAllPlan
AS
SELECT     [Plan].*, Product.producName
FROM         [Plan] INNER JOIN
                      Product ON [Plan].productID = Product.productID

GO
-----------------------------------getPlanByProductID-------------------
CREATE PROC getPlanByProductID
	@productID int
AS
SELECT     [Plan].*, Product.producName
FROM         [Plan] INNER JOIN
                      Product ON [Plan].productID = Product.productID
Where [Plan].productID=@productID
GO
------------------getPlanById-------------
CREATE PROC getPlanById
	@planID int
AS
SELECT  * from [Plan] where planID=@planID
GO

